/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.CTPNhapKho;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Khôi
 */
public class CTPNhapKhoDao {

	public static ArrayList<CTPNhapKho> layDanhSach() {
		ArrayList<CTPNhapKho> ds = new ArrayList<CTPNhapKho>();
		String sql = "SELECT  SoPNK, MaSP,SoLuong,GiaNhap   FROM  CTPNhapKho ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int so = rs.getInt("SoPNK");
				int ma = rs.getInt("MaSP");
				int sl = rs.getInt("SoLuong");
				double gia = rs.getFloat("GiaNhap");
				CTPNhapKho tmp = new CTPNhapKho(so, ma, sl, gia);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(CTPNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<CTPNhapKho> layDanhSachBySoPNK(int SoPNK) {
		ArrayList<CTPNhapKho> ds = new ArrayList<CTPNhapKho>();

		String sql = "SELECT SoPNK, MaSP,SoLuong,GiaNhap  FROM CTPNhapKho WHERE SoPNK = " + SoPNK;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int so = rs.getInt("SoPNK");
				int ma = rs.getInt("MaSP");
				int sl = rs.getInt("SoLuong");
				double gia = rs.getFloat("GiaNhap");
				CTPNhapKho tmp = new CTPNhapKho(so, ma, sl, gia);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(CTPNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<CTPNhapKho> layDanhSachByMaSP(int MaSP) {
		ArrayList<CTPNhapKho> ds = new ArrayList<CTPNhapKho>();

		String sql = "SELECT SoPNK, MaSP,SoLuong,GiaNhap  FROM CTPNhapKho WHERE MaSp = " + MaSP;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int so = rs.getInt("SoPNK");
				int ma = rs.getInt("MaSP");
				int sl = rs.getInt("SoLuong");
				double gia = rs.getFloat("GiaNhap");
				CTPNhapKho tmp = new CTPNhapKho(so, ma, sl, gia);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(CTPNhapKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static boolean them(ArrayList<CTPNhapKho> tmp) {
		boolean kq = false;

		String sql = "";

		String sqlUpdate = "";



		for (int i = 0; i < tmp.size(); i++) {
			String s = "INSERT INTO  CTPNhapKho  (SoPNK,MaSP,SoLuong,GiaNhap) VALUES ";
			s += String.format("( %d, %d,%d,%f);", tmp.get(i).getSoPNK(), tmp.get(i).getMaSP(), tmp.get(i).getSoLuong(), tmp.get(i).getGiaNhap());

			sql = sql + s;

			String sup = String.format("UPDATE SanPham SET "
				 + "SLTon = SLTon +%d "
				 + "WHERE  MaSP = %d;", tmp.get(i).getSoLuong(), tmp.get(i).getMaSP());
			sqlUpdate = sqlUpdate + sup;
		}
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
			int nu = cn.thucThi(sqlUpdate);

		} catch (Exception ex) {
			Logger.getLogger(CTPNhapKhoDao.class
				 .getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n >= 1) {
			kq = true;
		}
		return kq;
	}

	public static boolean them(CTPNhapKho tmp) {
		boolean kq = false;
		String sql = "INSERT INTO  CTPNhapKho  (SoPNK,MaSP,SoLuong,GiaNhap) VALUES ";

		String s = String.format("( %d, %d,%d,%f)", tmp.getSoPNK(), tmp.getMaSP(), tmp.getSoLuong(), tmp.getGiaNhap());
		s += sql;
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);


		} catch (Exception ex) {
			Logger.getLogger(CTPNhapKhoDao.class
				 .getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean xoa(int SoPNK) {
		boolean kq = false;
		String sql = String.format("DELETE FROM CTPNhapKho WHERE SoPNK=%d", SoPNK);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);


		} catch (Exception ex) {
			Logger.getLogger(CTPNhapKhoDao.class
				 .getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n >= 1) {
			kq = true;
		}
		return kq;
	}

	public static boolean xoa(int SoPNK, int MaSP) {
		boolean kq = false;
		String sql = String.format("DELETE FROM CTPNhapKho WHERE SoPNK=%d AND MaSP = %d", SoPNK, MaSP);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);


		} catch (SQLException ex) {
			Logger.getLogger(CTPNhapKhoDao.class
				 .getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean capNhat(CTPNhapKho tmp) {
		boolean kq = false;
		String sql = String.format("UPDATE CTPNhapKho"
			 + "SET"
			 + "SoLuong = %d, "
			 + "GiaNhap =%f "
			 + "WHERE  SoPNK = %d AND MaSP =%d; ", tmp.getSoLuong(), tmp.getSoPNK(), tmp.getSoPNK(), tmp.getGiaNhap());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);


		} catch (Exception ex) {
			Logger.getLogger(CTPNhapKhoDao.class
				 .getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static CTPNhapKho layByMa(int SoPNK, int MaSP) {
		CTPNhapKho tmp = null;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			String sql = String.format("SELECT * FROM CTPNhapKho WHERE SoPNK =%d AND MaSP =%d", SoPNK, MaSP);
			cn = new KetNoi();
			rs = cn.docBang(sql);
			if (rs.next()) {
				int so = rs.getInt("SoPNK");
				int ma = rs.getInt("MaSP");
				int sl = rs.getInt("SoLuong");
				double gia = rs.getFloat("GiaNhap");
				tmp = new CTPNhapKho(so, ma, sl, gia);


			}

		} catch (Exception ex) {
			Logger.getLogger(CTPNhapKhoDao.class
				 .getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return tmp;
	}
}
